/*
 * CloudBank Mobile Knowledge Sharing for Language Learners
 * Supported by the JISC Rapid Innovation Programme #jiscri
 *
 * Copyright (C) 2010 University of Brighton
 *
 * Licensed under the GNU General Public License v3 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.gnu.org/licenses/gpl.html
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 */
package uk.ac.brighton.itrg.cloudbank;

import java.io.File;
import java.net.URI;
import java.util.HashMap;
import java.util.Iterator;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;

/* ================================================================================================
 *  Database abstraction layer for local and cloud database. Exposes static methods 
 *  to read and write to 'the database', uses internal cache for better performance.
 *  
 *  local:  SQLite database
 *          mirrors locally edited content from the cloud database, i.e. entries that are 
 *          created or edited by _this user are saved to the cloud and then stored locally 
 *          under the same _id.   
 *         
 *  remote: REST server 
 *          queries and writes to the server
 *          
 *  cache:  HashMap of CachedCursor objects 
 *          time-stamps and stores remote cursors. Cursors become invalid when getting older 
 *          than CB.CACHE_MAX_AGE
 *          
 *  notes:  The local database is kept very simple on purpose, as all the heavy lifting is done
 *          in the cloud. Locally we just store item.id, item.item, item.modified. This is 
 *          enough information to produce a date-sorted list of recently edited items in the
 *          welcome screen, and to retrieve item data from the server. This minimalistic approach  
 *          avoids data inconsistencies when both, local and cloud databases.
 *          
 *          We completely encapsulate the data handling here and only expose high level calls 
 *          to query or write data. Obviously, the specific structure of returned data sets 
 *          must be known to logic and UI, which introduces an ugly dependency. However, in 
 *          reality a clean separation between data layer and logic cannot be achieved anyway.
 *  
 * ============================================================================================== */


public class DB 
{
	private static final String TAG = DB.class.getSimpleName();

	// database name and version
	private static final int VERSION 		= 1;
	private static final String NAME 		= "cloudbank.db";
	
	// SQL queries for local database
	private static final int CREATE			= 0;
	private static final int DROP			= 1;
	private static final int ZAP			= 2;
	private static final int INSERT			= 3;
	private static final int UPDATE			= 4;
	private static final int DELETE			= 5;
	private static final int ITEM			= 6;
	private static final int ITEMLIST		= 7;
	private static final int UPDATE_SILENT	= 8;
	private static final String[]SQL = 
	{
		"CREATE TABLE items (_id INTEGER PRIMARY KEY, item TEXT NOT NULL, modified TEXT default CURRENT_TIMESTAMP)",
		"DROP TABLE IF EXISTS items",
		"DELETE FROM items",
		"INSERT INTO items (_id, item) VALUES (%0, %1)",
		"UPDATE items SET item=%1, modified=CURRENT_TIMESTAMP WHERE _id=%0",
		"DELETE FROM items WHERE _id=%0",
		"SELECT _id FROM items WHERE _id=%0",
		"SELECT _id, item FROM items WHERE item LIKE %0 ORDER BY %1",
		"UPDATE items SET item=%1 WHERE _id=%0" /* silent */
	};

	
	// Local SQLite database for personal word list
	private static class DBHelper extends SQLiteOpenHelper
	{
		DBHelper(Context c)
		{
			super(c, NAME, null, VERSION);
		}	
		
		public void onCreate(SQLiteDatabase db)
		{
			Logger.log("DBHelper.onCreate()");
			db.beginTransaction();
			try
			{
				db.execSQL(SQL[CREATE]);
				db.setTransactionSuccessful();
			}
			catch(Exception e)
			{
				Logger.log(e);
			}
			finally
			{
				db.endTransaction();
			}
			Logger.log("ok");
		}
		
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			Logger.log("DBHelper.onUpgrade()");
			db.beginTransaction();
			try
			{
				db.execSQL(SQL[DROP]);
				db.execSQL(SQL[CREATE]);
				db.setTransactionSuccessful();
			}
			catch(Exception e)
			{
				Logger.log(e);
			}
			finally
			{
				db.endTransaction();
			}

			Logger.log("ok");
		}
	}
	
	
	// ------------------------------------------
	// our local database helper 
	// ------------------------------------------
	private static DBHelper mDBHelper;
	

		
	// ------------------------------------------
	// backup / restore /delete / zap the **local database 
	// ------------------------------------------
	public static void backupDatabase(Context context)
	{
		Logger.log(TAG + ".backupDatabase()");
        try {CB.fileCopy(context.getDatabasePath(NAME), getBackupPath());}
        catch(Exception e) {Logger.log(e);}
	}
	
	public static void restoreDatabase(Context context)
	{
		Logger.log(TAG + ".restoreDatabase()");
        try {CB.fileCopy(getBackupPath(), context.getDatabasePath(NAME));}
        catch(Exception e) {Logger.log(e);}
	}
	public static void deleteDatabase(Context context)
	{
		Logger.log(TAG + ".deleteDatabase()");
		context.deleteDatabase(NAME);
	}
	public static void zapDatabase(Context context)
	{
		Logger.log(TAG + ".zapDatabase()");
        if(mDBHelper == null) mDBHelper = new DBHelper(context);
        SQLiteDatabase db = mDBHelper.getWritableDatabase();  
        Logger.log("SQL", SQL[ZAP]);
		db.execSQL(SQL[ZAP]);
	}
	

	// ------------------------------------------
	// generate path to DB backup file
	// ------------------------------------------
	private static File getBackupPath()
	{
		File backup_folder = new File(Environment.getExternalStorageDirectory(), DB.class.getName());
		backup_folder.mkdirs();
		File backupDB = new File(backup_folder, "db.sqlite");
		return backupDB;
	}

	
	
	// ------------------------------------------
	// get list of items from **local database 
	// ------------------------------------------
	public static Cursor getLocalItemList(String search, String sort, Context context)
	{
        Logger.log(TAG + ".getLocalItemList()");
        Logger.log("search", search);
        Logger.log("sort", sort);

        if(search == null) search = quote("%");
        else               search = quote(search);
        
        if(sort == null)   sort = "modified COLLATE NOCASE DESC";
        else               sort = sort + " COLLATE NOCASE ASC";
        
        if(mDBHelper == null) mDBHelper = new DBHelper(context);
        SQLiteDatabase db = mDBHelper.getReadableDatabase();
		String sql = prepare(SQL[ITEMLIST], search, sort);
        Logger.log("SQL", sql);
        Cursor c = db.rawQuery(sql, null);
        Logger.log("Cursor", c);
        
		return c;
	}

	
	
	// ------------------------------------------
	// get item details from local DB
	// ------------------------------------------
	public static Cursor getLocalItem(long id, Context context)
	{
        Logger.log(TAG + ".getLocalItem()");
        Logger.log("id", id);

        if(mDBHelper == null) mDBHelper = new DBHelper(context);
        SQLiteDatabase db = mDBHelper.getReadableDatabase();        		
		String sql = prepare(SQL[ITEM], id);
        Cursor c = db.rawQuery(sql, null);
        Logger.log("Cursor", c);
        
		return c;
	}
	
	
	// ------------------------------------------
	// store item details in local DB
	// ------------------------------------------
	// note: another place where we write to the 
	// local DB is when updating values with 
	// current server content in 
	// public static Cursor getItem(long id, Context context)
	// ------------------------------------------
	public static void putLocalItem(Item item, Context context)
	{
        Logger.log(TAG + ".putLocalItem()");
        Logger.log("item", item);

        // first read to see if we need to insert or update
		if(mDBHelper == null) mDBHelper = new DBHelper(context);
		SQLiteDatabase db = mDBHelper.getWritableDatabase();        		
		String sql = prepare(SQL[ITEM], item._id);
        Cursor c = db.rawQuery(sql, null);
        
        if(c.getCount()>0)	sql = prepare(SQL[UPDATE], item._id, quote(item.item));
        else				sql = prepare(SQL[INSERT], item._id, quote(item.item));

        Logger.log("SQL", sql);
		db.execSQL(sql);
	}
	
	
	// ------------------------------------------
	// delete item details from local DB
	// ------------------------------------------
	public static void deleteLocalItem(long id, Context context)
	{
        Logger.log(TAG + ".deleteLocalItem()");
        Logger.log("id", id);

        if(mDBHelper == null) mDBHelper = new DBHelper(context);
        SQLiteDatabase db = mDBHelper.getWritableDatabase();  
		String sql = prepare(SQL[DELETE], id);

        Logger.log("SQL", sql);
		db.execSQL(sql);
	}
	

	// ------------------------------------------
	// error status for global access 
	// ------------------------------------------
	public static final int NOERROR = 0;
	public static final int ERR_UNKNOWN = 1;
	public static final int ERR_NETWORK = 2;
	public static final int ERR_SERVER = 3;
	public static final int ERR_XMLPARSE = 4;
	public static final int ERR_ENCODING = 5;
	public static final int ERR_ITEM404 = 6;
	public static final int ERR_MAX = 7;

	private static int mLastErrorCode = NOERROR;
	public static void resetError()			{mLastErrorCode = NOERROR;}
	public static void setError(int code)	{if(mLastErrorCode == NOERROR) mLastErrorCode = code;}
	public static int getLastErrorCode()	{return mLastErrorCode;}
	public static boolean errorState()		{return (mLastErrorCode != NOERROR);}

	private static String [] mErrorMessages = null;
	public static String getLastErrorMessage(Context context)
	{
		if(mErrorMessages == null)
		{
			mErrorMessages = new String[ERR_MAX];
			mErrorMessages[NOERROR] = null;
			mErrorMessages[ERR_UNKNOWN] = context.getResources().getString(R.string.err_unknown);
			mErrorMessages[ERR_NETWORK] = context.getResources().getString(R.string.err_network);
			mErrorMessages[ERR_SERVER] = context.getResources().getString(R.string.err_server);
			mErrorMessages[ERR_XMLPARSE] = context.getResources().getString(R.string.err_xmlparse);
			mErrorMessages[ERR_ENCODING] = context.getResources().getString(R.string.err_encoding);
			mErrorMessages[ERR_ITEM404] = context.getResources().getString(R.string.err_item404);
		}
		return mErrorMessages[mLastErrorCode];
	}
	
	

	
	// ------------------------------------------
	// we cache server data to speed up the UI.
	// ------------------------------------------
	private static final HashMap mCache = new HashMap();
	private static Cursor getCachedCursor(Object key)
	{
        if(!mCache.containsKey(key)) return null;
       	CachedCursor cc = (CachedCursor)(mCache.get(key));
        if(cc.getAge() > CB.CACHE_MAX_AGE) return null;
        Logger.log("read from cache", key);
        return cc.getCursor();
	}
	public static void clearCache() {mCache.clear();}

	
	
	// ------------------------------------------
	// get list of items from the **cloud
	// ------------------------------------------
	public static Cursor getItemList(String intag, String extag, String search, String sort, boolean forceRefresh)
	{
        Logger.log(TAG + ".getItemList()");
        Logger.log("intag", intag);
        Logger.log("extag", extag);
        Logger.log("search", search);
        Logger.log("sort", sort);
        
    	resetError();
    	String key = HTTPPost.Request.GET_ITEM_LIST + intag + extag + search + sort;
    	
    	Cursor c = null;
    	if(!forceRefresh) c = getCachedCursor(key); // might be null anyway!

        if(c == null)
        {
            Logger.log("query from server", true);
            HTTPPost post = new HTTPPost(HTTPPost.Request.GET_ITEM_LIST);
            if(intag != null) post.addString("intag", intag);
            if(extag != null) post.addString("extag", extag);
            if(search != null) post.addString("search", search);
            if(sort != null) post.addString("sort", sort);
            String xml = post.post();
            
            if(xml == null)
            {
            	setError(ERR_NETWORK);
            }
            else
            {
	            String cols[] = {"_id", "item"};
	            c = new ArrayCursor(cols, xml);
	            if(!errorState())
	            	mCache.put(key, new CachedCursor(c));
            }
        }
        return c;
	}


	
	
	// ------------------------------------------
	// get tag list from the **cloud
	// SORT DESC by count(relations to item) 
	// optionally limit results to n rows.
	// ------------------------------------------
	public static Cursor getTagList(int limit)
	{
        Logger.log(TAG + ".getTagList()");
        Logger.log("limit", limit);

    	resetError();
    	String key = HTTPPost.Request.GET_TAG_LIST + limit;
    	Cursor c = getCachedCursor(key);

    	if(c == null)
        {
            Logger.log("read from server", true);
            HTTPPost post = new HTTPPost(HTTPPost.Request.GET_TAG_LIST);
            if(limit > 0)
            {
            	post.addInteger("limit", limit);
            }
            String xml = post.post();

            if(xml == null)
            {
            	setError(ERR_NETWORK);
            }
            else
            {
                String[] cols = {"_id", "tag", "ref_count"};
                c = new ArrayCursor(cols, xml);
                
                if(!errorState())
                	mCache.put(key, new CachedCursor(c));
            }
        }
    		
        return c;
	}


	
	
	
	// ------------------------------------------
	// look up item details from the **cloud 
	// used to check for existing entries when 
	// adding new content. not stored in local DB!
	// ------------------------------------------
	public static Cursor getItem(String item)
	{
        Logger.log(TAG + ".getItem()");
        Logger.log("item", item);

        resetError();
    	String key = HTTPPost.Request.GET_ITEM + item;
    	Cursor c = getCachedCursor(key);
    	
    	if(c == null)
        {
            Logger.log("read from server", true);
            HTTPPost post = new HTTPPost(HTTPPost.Request.GET_ITEM);
           	post.addString("item", item);
            String xml = post.post();
    		
            if(xml == null)
            {
            	setError(ERR_NETWORK);
            }
            else
            {
                String[] cols = {"_id", "item", "description", "link", "image", "audio", "tags"};
                c = new ArrayCursor(cols, xml);
                
                if(!errorState())
                {
                    if(c.getCount() == 0)
                    {
                    	setError(ERR_ITEM404);
                    }
                    else
                    {
                    	mCache.put(key, new CachedCursor(c));
                    }
                }
            }
        }
        return c;
	}
	
	
	
	// ------------------------------------------
	// get item details from the **cloud
	// ------------------------------------------
	public static Cursor getItem(long id, Context context)
	{
        Logger.log(TAG + ".getItem()");
        Logger.log("id", id);

        String item = ""; // variable is used to update of local database
        
        resetError();
    	String key = HTTPPost.Request.GET_ITEM + id;
    	Cursor c = getCachedCursor(key);
    	
    	if(c == null)
        {
            Logger.log("read from server", true);
            HTTPPost post = new HTTPPost(HTTPPost.Request.GET_ITEM);
           	post.addLong("item", id);
            String xml = post.post();
    		
            if(xml == null)
            {
            	setError(ERR_NETWORK);
            }
            else
            {
                String[] cols = {"_id", "flag", "item", "description", "link", "image", "audio", "tags"};
                c = new ArrayCursor(cols, xml);
                
                if(!errorState())
                {
                    if(c.getCount() == 0)
                    {
                    	setError(ERR_ITEM404);
                    }
                    else
                    {
                    	// retrieve item to update the local database.
                    	// note: column index corresponds to cosl[] array!!!
                    	item = cols[2];  
                    	mCache.put(key, new CachedCursor(c));
                    }
                }
            }
            
            // update local database...
            if(!errorState()) 
            {
            	// if item is successfully retrieved from server, update  
                // entry just in case the spelling has changed. 
            	// we don't test if the item is stored locally because
            	// the UPDATE condition does that implicitly...

            	if(mDBHelper == null) mDBHelper = new DBHelper(context);
        		SQLiteDatabase db = mDBHelper.getWritableDatabase();        		
    			String sql = prepare(SQL[UPDATE_SILENT], id, quote(item));
                Logger.log("SQL", sql);
    			db.execSQL(sql);
            }
            else if(mLastErrorCode == ERR_ITEM404) 
            {
                // if item was not found on server, remove any local reference.
            	// we don't test if the item is stored locally because
            	// the DELETE condition does that implicitly...

        		if(mDBHelper == null) mDBHelper = new DBHelper(context);
        		SQLiteDatabase db = mDBHelper.getWritableDatabase();        		
    			String sql = prepare(SQL[DELETE], id);
                Logger.log("SQL", sql);
    			db.execSQL(sql);
            } 
            
        }
        return c;
	}
	
	
	
	
	// ------------------------------------------
	// insert or update an item in the **cloud
	// and store copy in **local database
	// ------------------------------------------
	public static long putItem(Item item, Context context)
	{
        Logger.log(TAG + ".putItem()");
        Logger.log("item", item);
        
        resetError();
        long last_updated = -1;
        
        try
        {
            HTTPPost post = new HTTPPost(HTTPPost.Request.PUT_ITEM);
           	post.addLong("_id", item._id);
           	post.addString("item", item.item);
           	post.addString("description", item.description);
           	post.addString("link", item.link);
           	post.addString("tags", item.tags);
           	if(isNewMedia(item.image))
           	{
           		post.addFile("image", 
           				     Media.getTmpImageFile(),
           		             Media.getTmpImageMime());
           	}
           	if(isNewMedia(item.audio)) 
           	{
           		post.addFile("audio", 
	      				     Media.getTmpAudioFile(),
	      		             Media.getTmpAudioMime());
           	}

            Logger.log("HTTPost", HTTPPost.Request.PUT_ITEM);
           	String xml = post.post();
            Logger.log("XML", xml);
           	
            if(xml == null)
            {
            	setError(ERR_NETWORK);
            }
            else
            {
	            String[] cols = {"_id"};
	            Cursor c = new ArrayCursor(cols, xml);
	            
                if(!errorState())
                {
		            if(c.moveToFirst())
		            {
		        		last_updated = c.getLong(0);
		
		            	if(last_updated >= 0)
		            	{
		            		item._id = last_updated;
		
		                    // ------------------------------------
		                    // update local database
		                    // ------------------------------------
		            		putLocalItem(item, context);
		        			
		        			
		                    // ------------------------------------
		                    // clear cache...
		                    // ------------------------------------
		            		
		        			// refresh object: media references might have changed.
		        			String key = HTTPPost.Request.GET_ITEM + item._id;
		                    mCache.remove(key);

		                    // clear lists: items and tags might have changed
		            		Iterator it = mCache.keySet().iterator();
		            		while(it.hasNext())
		            		{
		            			String k = (String)(it.next());
		            			if(k.startsWith(HTTPPost.Request.GET_ITEM_LIST) 
		            			|| k.startsWith(HTTPPost.Request.GET_TAG_LIST))
		            			{
		            				it.remove();
		            			}
		            		}
		            	}
		            }
                }
            }
        }
        catch(Exception e)
        {
        	setError(ERR_UNKNOWN);
        	Logger.log(e);
        }
        
        return last_updated;
 	}
	
	

	
	
	
	// ------------------------------------------
	// flag an item 
	// ------------------------------------------
	public static void flagItem(long id, int flag)
	{
        Logger.log(TAG + ".flagItem()");
        Logger.log("id", id);
        Logger.log("flag", flag);

        resetError();
    	
        HTTPPost post = new HTTPPost(HTTPPost.Request.FLAG_ITEM);
       	post.addLong("_id", id);
       	post.addInteger("flag", flag);
        String xml = post.post();
		
        if(xml == null)
        {
        	setError(ERR_NETWORK);
        }
        else
        {
            if(xml.startsWith("<error_response>"))
            {
            	setError(ERR_SERVER);
            }
        }
	}
	

	
	
	
	// ------------------------------------------
	// utility: replace %n in sql with actual values
	// ------------------------------------------
	private static String prepare(String sql, long id)
	{
		return sql.replaceFirst("%0", Long.toString(id));
	}
	private static String prepare(String sql, long id, String item)
	{
		return sql.replaceFirst("%0", Long.toString(id)).replaceFirst("%1", item);
	}
	private static String prepare(String sql, String search, String sort)
	{
		return sql.replaceFirst("%0", search).replaceFirst("%1", sort);
	}
	
	// ------------------------------------------
	// utility: place string in quotes
	// ------------------------------------------
	private static final String quote(String s)
	{
		return ("\"" + s + "\"");
	}
	
	// ------------------------------------------
	// utility: check if we have new media to upload
	// ------------------------------------------
	private static boolean isNewMedia(String s)
	{
		// Param s is item.image or item.audio. Both values are 
		// either ""
		// or     [relative path cloudbank server starting with app/media/...] for existing media 
		// or     [temp media filename] for newly recorded media
		return (s.equals(Media.getTmpImageUri().toString()) || s.equals(Media.getTmpAudioUri().toString()));
	}
		
}
